package db

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

const (
	wdSQLUser   = "root"
	wdSQLPasswd = "123"
	wdSQLHost   = "127.0.0.1"
	wdSQLPost   = "3306"
)

const (
	//StmtInsertCategory ...category(category_name,category_no,update_time)
	StmtInsertCategory = "InsertCategory"
	//StmtGetCategory ...select category_name,category_no,create_time,update_time
	StmtGetCategory = "StmtGetCategory"
	//StmtGetCategorys .. "select category_name,category_no,create_time,update_time from category where ID in(?)"
	StmtGetCategorys = "StmtGetCategorys"
	//StmtGetAllCategory ...Select all
	StmtGetAllCategory = "StmtGetAllCategory"
	//StmtInsertArticle ..category_id,content,title,view_count,comment_count,username,summary
	StmtInsertArticle = "StmtInsertArticle"
	//StmtGetPageArticle ..ID,category_id,title,view_count,comment_count,username,summary,create_time
	StmtGetPageArticle = "StmtGetPageArticle"
	//StmtGetArticle ..."select category_id,title,view_count,comment_count,username,summary,create_time,content where ID = ? limit 1"
	StmtGetArticle = "StmtGetArticle"
	//StmtGetSimilarArticles .."select ID,category_id，title,view_count,comment_count,username,summary,create_time from category where category_id=? and status = 1 limit ?"
	StmtGetSimilarArticles = "StmtGetSimilarArticles"
	//StmtGetComments .."select content,username,create_time from comment where article_id = ?"
	StmtGetComments = "StmtGetComments"
	//StmtInsertComment ..article_id,content,username,email
	StmtInsertComment = "StmtInsertComment"
	//StmtInsertLeave ..username,email,content
	StmtInsertLeave = "StmtInsertLeave"
	//StmtGetLeaves .."select username,email,content,create_time from leave  where limit 100 order by ID desc"
	StmtGetLeaves = "StmtGetLeaves"
)

//DB ...
var DB *sqlx.DB
var err error

//Stmt  预处理指令集
var Stmt = make(map[string]*sql.Stmt)
var stmporder = make(map[string]string)

//初始化数据库
func init() {
	dbpath := fmt.Sprintf("%s:%s@tcp(%s:%s)/blog", wdSQLUser, wdSQLPasswd, wdSQLHost, wdSQLPost)
	DB, err = sqlx.Open("mysql", dbpath)
	if err != nil {
		fmt.Println(err.Error())
		//panic(err.Error())
	}
	if err = DB.Ping(); err != nil {
		panic(err.Error())
	}
	DB.SetMaxOpenConns(100)
	DB.SetMaxIdleConns(16)

	ordertostmt() //声明指令集
	stmtInit()    //对指令集进行预处理
}
func stmtInit() {
	for k, v := range stmporder {
		Stmt[k], err = DB.Prepare(v)
		if err != nil {
			fmt.Println(k, "--->", err.Error())
			panic(err.Error())
		}
	}
}
func ordertostmt() {
	stmporder[StmtInsertCategory] = "insert into category(category_name,category_no,update_time) values(?,?,?)"
	stmporder[StmtGetCategory] = "select category_name,category_no,create_time,update_time from category where ID = ? limit 1"
	stmporder[StmtGetCategorys] = "select * from category where ID in(?)"
	stmporder[StmtGetAllCategory] = "select * from category limit 10"
	stmporder[StmtInsertArticle] = "insert into article(category_id,content,title,view_count,comment_count,username,summary) values(?,?,?,?,?,?,?)"
	stmporder[StmtGetPageArticle] = "select ID,category_id,title,view_count,comment_count,username,summary,create_time from article where ID >? and status = 1 order by ID desc limit ?"
	stmporder[StmtGetArticle] = "select category_id,title,view_count,comment_count,username,summary,create_time,content from article where ID = ? limit 1"
	stmporder[StmtGetSimilarArticles] = "select ID,category_id,title,view_count,comment_count,username,summary,create_time from article where category_id=? and status = 1 limit ?"
	stmporder[StmtGetComments] = "select content,username,create_time from comment where article_id = ? and status = 1"
	stmporder[StmtInsertComment] = "insert into comment(article_id,content,username,email) values(?,?,?,?)"
	stmporder[StmtInsertLeave] = "insert into leaves(username,email,content) values(?,?,?)"
	stmporder[StmtGetLeaves] = "select username,email,content,create_time from leaves order by ID desc limit 100"
}
